import json
from datetime import datetime
from typing import List, Dict, Optional, Any
from database.connection import get_db_connection


# 会话操作
async def create_session(session_id: str, initial_message: str) -> None:
    """创建新会话"""
    current_time = datetime.now().isoformat()
    conn = await get_db_connection()
    try:
        await conn.execute("""
                           INSERT INTO sessions (id, created_at, updated_at, last_message)
                           VALUES (?, ?, ?, ?)
                           """, (session_id, current_time, current_time, initial_message))
        await conn.commit()
    finally:
        await conn.close()


async def update_session(session_id: str, last_message: str) -> None:
    """更新会话"""
    current_time = datetime.now().isoformat()
    conn = await get_db_connection()
    try:
        # await conn.execute("""
        #                    UPDATE sessions
        #                    SET updated_at   = ?,
        #                        last_message = ?
        #                    WHERE id = ?
        #                    """, (current_time, last_message, session_id))
        await conn.execute("""
                           UPDATE sessions
                           SET updated_at   = ?,
                               last_message = case when last_message is null then ? else last_message end
                           WHERE id = ?
                           """, (current_time, last_message, session_id))
        await conn.commit()
    finally:
        await conn.close()


async def get_session(session_id: str) -> Optional[Dict[str, Any]]:
    """获取会话信息"""
    conn = await get_db_connection()
    try:
        cursor = await conn.execute("SELECT * FROM sessions WHERE id = ?", (session_id,))
        session = await cursor.fetchone()
        return dict(session) if session else None
    finally:
        await conn.close()


async def get_all_sessions() -> List[Dict[str, Any]]:
    """获取所有会话"""
    conn = await get_db_connection()
    try:
        cursor = await conn.execute("SELECT * FROM sessions ORDER BY updated_at DESC")
        sessions = await cursor.fetchall()
        return [dict(session) for session in sessions]
    finally:
        await conn.close()


async def delete_session(session_id: str) -> bool:
    """删除会话及消息"""
    conn = await get_db_connection()
    try:
        # 先删除消息
        await conn.execute("DELETE FROM messages WHERE session_id = ?", (session_id,))
        # 再删除会话
        cursor = await conn.execute("DELETE FROM sessions WHERE id = ?", (session_id,))
        await conn.commit()
        return cursor.rowcount > 0
    finally:
        await conn.close()


# 消息操作
async def add_message(session_id: str, content: str, is_user: bool) -> None:
    """添加消息"""
    message_id = f"msg_{datetime.now().timestamp()}"
    current_time = datetime.now().isoformat()
    conn = await get_db_connection()
    try:
        await conn.execute("""
                           INSERT INTO messages (id, session_id, content, is_user, created_at)
                           VALUES (?, ?, ?, ?, ?)
                           """, (message_id, session_id, content, is_user, current_time))
        await conn.commit()
    finally:
        await conn.close()


async def get_session_messages(session_id: str) -> List[Dict[str, Any]]:
    """获取会话消息"""
    conn = await get_db_connection()
    try:
        cursor = await conn.execute("""
                                    SELECT *
                                    FROM messages
                                    WHERE session_id = ?
                                    ORDER BY created_at ASC
                                    """, (session_id,))
        messages = await cursor.fetchall()
        return [dict(msg) for msg in messages]
    finally:
        await conn.close()


# Prompt模板操作
async def get_prompt_template(scenario: str, template_id: Optional[str] = None) -> Optional[Dict[str, Any]]:
    """获取指定场景的Prompt模板"""
    conn = await get_db_connection()
    try:
        if template_id:
            cursor = await conn.execute(
                "SELECT * FROM prompt_templates WHERE id = ?",
                (template_id,)
            )
        else:
            cursor = await conn.execute(
                "SELECT * FROM prompt_templates WHERE scenario = ? AND is_default = 1",
                (scenario,)
            )

        template = await cursor.fetchone()
        if template:
            template_dict = dict(template)
            # 解析variables JSON字符串
            template_dict["variables"] = json.loads(template_dict["variables"])
            return template_dict
        return None
    finally:
        await conn.close()


async def get_all_templates() -> List[Dict[str, Any]]:
    """获取所有模板"""
    conn = await get_db_connection()
    try:
        cursor = await conn.execute("SELECT * FROM prompt_templates ORDER BY scenario")
        templates = await cursor.fetchall()
        result = []
        for template in templates:
            template_dict = dict(template)
            template_dict["variables"] = json.loads(template_dict["variables"])
            result.append(template_dict)
        return result
    finally:
        await conn.close()
